Solution to Exercise 1

Solution#

The ER diagram derived from our requirements is shown below. The diagram uses some advanced features, including relationships that have attributes and weak entity types.

Explanation#

In our design:

• STUDENT is a strong entity, with an identifier, Student_Id, created to be the primary key used to distinguish between students (remember, we could have several students with the same name).

• PROGRAM is a strong entity, with the identifier Program_Id as the primary key used to distinguish between programs.

• Each student must be enrolled in a program, so the STUDENT entity participates totally in the many-to-one ENROLLS_IN relationship with PROGRAM. A program can exist without having any enrolled students, so it participates partially in this relationship.

• A COURSE has meaning only in the context of a PROGRAM, so it’s a weak entity, with Course_Id as a weak key. This means that a COURSE entity is uniquely identified using its Course_Id and the Program_Id of its owning program.

• As a weak entity, COURSE participates totally in the many-to-one identifying relationship with its owning PROGRAM.

• STUDENT and COURSE are related through the many-to-many, ATTEMPTS relationships; a course can exist without a student, and a student can be enrolled without attempting any courses, so the participation is not total.

• When a student attempts a course, there are attributes needed to capture the Year, Semester, Mark and Grade of that course.